﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

using System.Text;
using Portal.Environment;
using Portal.Language;
using Portal.Interface;

namespace Portal.CDSP
{
    public class SearchBLL
    {

        public DataTable SelectAllGroup()
        {
            string Query = "select * from TableSuggestions order by time desc";
            SqlConnection sqlConnection = new SqlConnection(Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;

        }


        public DataTable ReadingGroupNews()
        {
            string Query = " select * from TableGroupNews where IsNews=1 order by [" + Environments.ActiveLanguage + "Subject]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;

            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }


        public DataTable ReadingGroupNewsItemsForSearch(string belongSite)
        {
            string Query = "select * from TableGroupNewsItem where belongSite=@belongSite and GroupNewsItemId in (select fk_GroupNewsItem_Id from TableNews where belongSite=@belongSite) order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@belongSite", SqlDbType.VarChar);
            sqlCommand.Parameters["@belongSite"].Value = belongSite;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }

        public DataTable ReadingGroupNewsItems(string GroupNewsId, string ParentGroupNewsId)
        {
            string Query = "select * from TableGroupNewsItem where fk_GroupNews_Id=@GroupNewsId and IsGroupNews = 1";
            if (ParentGroupNewsId != null)
            {
                Query += " and fk_ParentGroupNewsItem_Id='" + ParentGroupNewsId + "' ";
            }
            else
            {
                Query += " and fk_ParentGroupNewsItem_Id is null";
            }

            Query += " order by [IndexOrder]";
            SqlConnection sqlConnection = new SqlConnection(Environment.Environments.ConnectionString);
            sqlConnection.Open();
            SqlCommand sqlCommand = sqlConnection.CreateCommand();
            sqlCommand.CommandText = Query;
            sqlCommand.Parameters.Add("@GroupNewsId", SqlDbType.VarChar);
            sqlCommand.Parameters["@GroupNewsId"].Value = GroupNewsId;
            DataTable dtResult = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(dtResult);
            return dtResult;
        }
    }
}
